﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Data;
using NewsMine.Utilities;
using NewsMine.DomainObjects;

namespace NewsMine.Storage.SQLite
{
    public class SQLiteFeedStoreHelper
    {
        string filePath = null;
        string connectionString = null;
        string readOnlyConnectionString = null;

        string folderPath = string.Empty;
        object syncObject = new object();


        int pandingItemsAllowedMaxValue = 500;

        object collectionLock = new object();

        Dictionary<string, byte[]> pendingSetOperationsDictionary = new Dictionary<string, byte[]>();
        List<string> pendingRemovalKeys = new List<string>();


        Dictionary<string, bool> frequentKeys = new Dictionary<string, bool>();

        Dictionary<string, bool> FrequentKeys
        {
            get
            {
                lock (collectionLock)
                {
                    return frequentKeys;
                }
            }
        }

        string createTableScript = "CREATE TABLE [Feed] ([Language] NVARCHAR(20)  NULL,[Author] NVARCHAR(2000)  NULL,[Title] NVARCHAR(2000)  NULL,[Description] NVARCHAR(2000)  NULL,[category] NVARCHAR(2000)  NULL,[tags] NVARCHAR(2000)  NULL,[Keyworkds] NVARCHAR(2000)  NULL, [body] NVARCHAR(2000)  NULL, [Link] NVARCHAR(3000)  NULL,[domain] VARCHAR(30)  NULL,[ImageUrl] VARCHAR(200)  NULL,[refurl] vaRCHAR(1000)  NULL,[ThumbImageUrl] varCHAR(2000)  NULL,[rank] inTEGER  NULL,[pubdate] nUMERIC  NULL,[UpdatedDateTime] nUMERIC  NULL,[lasttouchtime] nuMERIC  NULL,[ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT)";

        string createIndexScript = "CREATE INDEX [Feed_index] ON [Feed]( [Language]  ASC,[domain]  ASC,[refurl]  ASC,[rank]  ASC,[pubdate]  ASC,[UpdatedDateTime]  ASC,[lasttouchtime]  ASC,[ID]  ASC)";
        string sqlForIdentityValue = "SELECT last_insert_rowid()";

        public SQLiteFeedStoreHelper(string sqliteFilePath)
        {
            folderPath = Path.GetDirectoryName(sqliteFilePath);

            if (!Directory.Exists(folderPath))
                Directory.CreateDirectory(folderPath);

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    filePath = sqliteFilePath;
                    connectionString = string.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=100;UTF8Encoding=True", filePath);

                    readOnlyConnectionString = string.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=100;Read Only=True;UTF8Encoding=True", filePath);

                    if (File.Exists(filePath) == false)
                    {
                        var connection = new SQLiteConnection(connectionString);

                        try
                        {
                            connection.Open();

                            using (var command = new SQLiteCommand(createTableScript, connection))
                            {
                                command.ExecuteNonQuery();
                            }

                            using (var command = new SQLiteCommand(createIndexScript, connection))
                            {
                                command.ExecuteNonQuery();
                            }

                        }
                        catch (Exception e)
                        {
                            throw;
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                }
            }

        }

        public void DeleteByID(long id)
        {
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (var command = new SQLiteCommand("delete from Feed where id = @id", connection))
                        {
                            command.Parameters.Add("@id", DbType.UInt64).Value = id;

                            command.ExecuteNonQuery();
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public void DeleteByDomain(string domain)
        {
            if (string.IsNullOrEmpty(domain))
                return;

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (var command = new SQLiteCommand("delete from Feed where domain = @domain", connection))
                        {
                            command.Parameters.Add("@domain", DbType.String).Value = domain;

                            command.ExecuteNonQuery();
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public FeedItem AddFeedToStore(FeedItem feedItem)
        {
            List<FeedItem> feed = new List<FeedItem>();
            feed.Add(feedItem);

            var result = AddFeedToStore(feed);

            if (result != null && result.Count > 0)
            {
                return result[0];
            }
            else
                return null;

        }

        public List<FeedItem> AddFeedToStore(List<FeedItem> feedItems)
        {
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    DateTime startTime = DateTime.UtcNow;

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (SQLiteTransaction tran = connection.BeginTransaction())
                        {

                            #region Add Operations

                           
                                for (int i = 0; i < feedItems.Count; i++)
                                {
                                    FeedItem feed = feedItems[i];

                                    feed.Sanitize();

                                    if (NewsMine.Utilities.Extensions.IsNumeric(feed.ID) == true && Convert.ToInt64(feed.ID) > 0)
                                    {
                                        using (var command = updateSqliteCommandForUpdatingFeed(feed, connection))
                                        {
                                            command.ExecuteNonQuery();
                                        }
                                    }
                                    else
                                    {
                                        using (var command = createSqliteCommandForInsertingFeed(feed, connection))
                                        {
                                            command.ExecuteNonQuery();
                                        }

                                        using (var command = new SQLiteCommand(sqlForIdentityValue, connection))
                                        {
                                            long lastID = (long)command.ExecuteScalar();

                                            if (lastID <= 0)
                                                throw new ApplicationException("Error while adding the feed in sqlite database");
                                            else
                                                feed.ID = lastID.ToString();
                                        }
                                    }
                                }
                            

                            #endregion

                            #region Set operations

                            //foreach (var key in pendingSetOperationsDictionary.Keys)
                            //{
                            //    var data = pendingSetOperationsDictionary[key];

                            //    bool isExists = false;

                            //    using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                            //    {


                            //        command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            //        int count = Convert.ToInt32(command.ExecuteScalar());

                            //        if (count > 0)
                            //            isExists = true;
                            //    }

                            //    if (isExists == true)
                            //    {
                            //        using (var command = new SQLiteCommand("update KeyValue set DATA=@data where Key = @key", connection))
                            //        {
                            //            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            //            if (data != null)
                            //                command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                            //            else
                            //                command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                            //            command.ExecuteNonQuery();
                            //        }
                            //    }
                            //    else
                            //    {
                            //        using (var command = new SQLiteCommand("INSERT INTO KeyValue (KEY,DATA) VALUES (@key,@data)", connection))
                            //        {
                            //            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            //            if (data != null)
                            //                command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                            //            else
                            //                command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                            //            command.ExecuteNonQuery();
                            //        }
                            //    }
                            //}

                            #endregion

                            tran.Commit();

                            return feedItems;

                        }
                    }
                    catch (SQLiteException ex)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();

                        DateTime endTime = DateTime.UtcNow;

                    }
                }
            }
        }

        public FeedItem GetFeedItemByID(long id)
        {
            long[] ids = { id };

            var resultedFeed = GetFeedItemsByIDs(ids);

            if (resultedFeed != null && resultedFeed.Count > 0)
            {
                return resultedFeed[0];
            }
            else
                return null;
        }

        public List<FeedItem> GetFeedItemsByIDs(long[] ids)
        {
            if (ids == null || ids.Length <= 0)
                return null;
            string selectQuery = string.Format("SELECT * FROM Feed WHERE id in ({0})", string.Join(",", ids));


            return GetFeedItemsBySqlQuery(selectQuery);
        }

        public List<FeedItem> GetFeedItemsBySqlQuery(string selectQuery)
        {
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        List<FeedItem> resultedFeeds = new List<FeedItem>();

                        using (var command = new SQLiteCommand(selectQuery, connection))
                        {
                            connection.Open();

                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    resultedFeeds.Add(GetFeedItem(reader));
                                }
                            }
                        }

                        return resultedFeeds;

                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public List<FeedItem> GetAllByPage(long startIdValue, int pageSize,out long nextStartIdValue)
        {
            string selectQuery = string.Format("SELECT * FROM Feed WHERE id > {0} ORDER BY id LIMIT {1}", startIdValue, pageSize);
            nextStartIdValue = -1;

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        List<FeedItem> resultedFeeds = new List<FeedItem>();

                        using (var command = new SQLiteCommand(selectQuery, connection))
                        {
                            connection.Open();

                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var feedFromDb = GetFeedItem(reader);
                                    resultedFeeds.Add(feedFromDb);

                                    nextStartIdValue = Convert.ToInt64(feedFromDb.ID);
                                }
                            }
                        }

                        return resultedFeeds;

                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        private FeedItem GetFeedItem(SQLiteDataReader reader)
        {
            FeedItem resultFeed = new FeedItem();

            resultFeed.Author = Convert.ToString(reader["Author"]);
            resultFeed.Body = Convert.ToString(reader["Body"]);
            resultFeed.category = Convert.ToString(reader["category"]);
            resultFeed.Description = Convert.ToString(reader["Description"]);
            resultFeed.Domain = Convert.ToString(reader["Domain"]);
            resultFeed.ID = Convert.ToString(reader["ID"]);
            resultFeed.ImageUrl = Convert.ToString(reader["ImageUrl"]);
            resultFeed.Keyworkds = Convert.ToString(reader["Keyworkds"]); //todo: needs to be included.
            resultFeed.Language = Convert.ToString(reader["Language"]);
            resultFeed.Link = Convert.ToString(reader["Link"]);

            float rank = float.NaN;
            if (float.TryParse(Convert.ToString(reader["Rank"]), out rank) == true)
            {
                resultFeed.Rank = rank;
            }

            resultFeed.RefUrl = Convert.ToString(reader["RefUrl"]);

            resultFeed.Tags = new List<string>(Convert.ToString(reader["tags"]).Split(Environment.NewLine.ToCharArray()));

            resultFeed.ThumbImageUrl = Convert.ToString(reader["ThumbImageUrl"]); //needs to change
            resultFeed.Title = Convert.ToString(reader["Title"]);

            resultFeed.PubDate = new DateTime(Convert.ToInt64(reader["PubDate"]));
            resultFeed.LastTouchTime = new DateTime(Convert.ToInt64(reader["LastTouchTime"]));
            resultFeed.UpdatedDateTime = new DateTime(Convert.ToInt64(reader["UpdatedDateTime"]));




            return resultFeed;
        }

        private SQLiteCommand createSqliteCommandForInsertingFeed(FeedItem feed, SQLiteConnection conn)
        {
            string insertQuery = "insert into Feed (Author,Language,Title,Description,category,body,tags,Keyworkds,Link,domain,ImageUrl,refurl,ThumbImageUrl,rank,pubdate,UpdatedDateTime,lasttouchtime)" +
                                          " values(@Author,@Language,@Title,@Description,@category,@body,@tags,@Keyworkds,@Link,@domain,@ImageUrl,@refurl,@ThumbImageUrl,@rank,@pubdate,@UpdatedDateTime,@lasttouchtime)";

            SQLiteCommand command = new SQLiteCommand(insertQuery, conn);

            command.Parameters.Add("@Author", DbType.String).Value = feed.Author;
            command.Parameters.Add("@Language", DbType.String).Value = feed.Language;
            command.Parameters.Add("@Title", DbType.String).Value = feed.Title;
            command.Parameters.Add("@Description", DbType.String).Value = feed.Description;
            command.Parameters.Add("@category", DbType.String).Value = feed.category;
            command.Parameters.Add("@body", DbType.String).Value = feed.Body;

            if (feed.Tags != null && feed.Tags.Count > 0)
                command.Parameters.Add("@tags", DbType.String).Value = string.Join(Environment.NewLine, feed.Tags.ToArray<string>());
            else
                command.Parameters.Add("@tags", DbType.String).Value = "";

            command.Parameters.Add("@Keyworkds", DbType.String).Value = feed.Keyworkds;

            command.Parameters.Add("@category", DbType.String).Value = feed.category;

            command.Parameters.Add("@Link", DbType.String).Value = feed.Link;
            command.Parameters.Add("@domain", DbType.String).Value = feed.Domain;
            command.Parameters.Add("@ImageUrl", DbType.String).Value = feed.ImageUrl;
            command.Parameters.Add("@refurl", DbType.String).Value = feed.RefUrl;
            command.Parameters.Add("@ThumbImageUrl", DbType.String).Value = feed.ThumbImageUrl;

            command.Parameters.Add("@rank", DbType.Single).Value = feed.Rank;
            command.Parameters.Add("@pubdate", DbType.Int64).Value = feed.PubDate.Ticks;
            command.Parameters.Add("@UpdatedDateTime", DbType.Int64).Value = feed.UpdatedDateTime.Ticks;
            command.Parameters.Add("@lasttouchtime", DbType.Int64).Value = feed.LastTouchTime.Ticks;

            return command;

        }

        private SQLiteCommand updateSqliteCommandForUpdatingFeed(FeedItem feed, SQLiteConnection conn)
        {
            string updateQuery = "update Feed SET Author=@Author,Language=@Language,Title=@Title,Description=@Description,category=@category,body=@body,tags=@tags,Keyworkds=@Keyworkds,Link=@Link,domain=@domain,ImageUrl=@ImageUrl,refurl=@refurl,ThumbImageUrl=@ThumbImageUrl,rank=@rank,pubdate=@pubdate,UpdatedDateTime=@UpdatedDateTime,lasttouchtime=@lasttouchtime " +
                                          " where id=@id";

            SQLiteCommand command = new SQLiteCommand(updateQuery, conn);

            command.Parameters.Add("@id", DbType.VarNumeric).Value = feed.ID;

            command.Parameters.Add("@Author", DbType.String).Value = feed.Author;
            command.Parameters.Add("@Language", DbType.String).Value = feed.Language;
            command.Parameters.Add("@Title", DbType.String).Value = feed.Title;
            command.Parameters.Add("@Description", DbType.String).Value = feed.Description;
            command.Parameters.Add("@category", DbType.String).Value = feed.category;
            command.Parameters.Add("@body", DbType.String).Value = feed.Body;

            if (feed.Tags != null && feed.Tags.Count > 0)
                command.Parameters.Add("@tags", DbType.String).Value = string.Join(Environment.NewLine, feed.Tags.ToArray<string>());
            else
                command.Parameters.Add("@tags", DbType.String).Value = "";

            command.Parameters.Add("@Keyworkds", DbType.String).Value = feed.Keyworkds;

            command.Parameters.Add("@category", DbType.String).Value = feed.category;

            command.Parameters.Add("@Link", DbType.String).Value = feed.Link;
            command.Parameters.Add("@domain", DbType.String).Value = feed.Domain;
            command.Parameters.Add("@ImageUrl", DbType.String).Value = feed.ImageUrl;
            command.Parameters.Add("@refurl", DbType.String).Value = feed.RefUrl;
            command.Parameters.Add("@ThumbImageUrl", DbType.String).Value = feed.ThumbImageUrl;

            command.Parameters.Add("@rank", DbType.Single).Value = feed.Rank;
            command.Parameters.Add("@pubdate", DbType.Int64).Value = feed.PubDate.Ticks;
            command.Parameters.Add("@UpdatedDateTime", DbType.Int64).Value = feed.UpdatedDateTime.Ticks;
            command.Parameters.Add("@lasttouchtime", DbType.Int64).Value = feed.LastTouchTime.Ticks;

            return command;

        }


        #region Old functions frm the key value store

        public void Set(string key, byte[] data)
        {
          


            string constantIdentifier = DateTime.Now.Ticks.ToString() + "_sqlite_setmethod ";

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    TempLogger.LogCustom("sqlitePerformance.txt", " start of the " + constantIdentifier);

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        bool isExists = false;

                        using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                        {

                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            int count = Convert.ToInt32(command.ExecuteScalar());

                            if (count > 0)
                                isExists = true;
                        }

                        if (isExists == true)
                        {
                            using (var command = new SQLiteCommand("update KeyValue set DATA=@data where Key = @key", connection))
                            {
                                command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                if (data != null)
                                    command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                else
                                    command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                command.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            using (var command = new SQLiteCommand("INSERT INTO KeyValue (KEY,DATA) VALUES (@key,@data)", connection))
                            {
                                command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                if (data != null)
                                    command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                else
                                    command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                command.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();

                        TempLogger.LogCustom("sqlitePerformance.txt", " End of the " + constantIdentifier);
                    }
                }
            }

        }

        private void MakeSurePendingSetOperationsAndRemoveOperationsArePersisted()
        {
            if (pendingSetOperationsDictionary.Count >= pandingItemsAllowedMaxValue)
            {
                DoPersistPendingStuffToDiskFile();

                DoPendingBulkOperations();
            }
            else
                DoPersistPendingStuffToDiskFile();

        }

        private void DoPersistPendingStuffToDiskFile()
        {
            FileExetensions.SerializeObjectToFile(Path.Combine(folderPath, "Removables.db"), pendingRemovalKeys);
            FileExetensions.SerializeObjectToFile(Path.Combine(folderPath, "Settables.db"), pendingSetOperationsDictionary);
        }

        private void DoReadFromPersistedPendingStuffFromDiskFile()
        {
            string removablesFile = Path.Combine(folderPath, "Removables.db");
            if (File.Exists(removablesFile))
                pendingRemovalKeys = (List<string>)FileExetensions.DeserializeFromFile(removablesFile);

            string settablesFile = Path.Combine(folderPath, "Settables.db");
            if (File.Exists(settablesFile))
                pendingSetOperationsDictionary = (Dictionary<string, byte[]>)FileExetensions.DeserializeFromFile(settablesFile);

        }

        private void DoPendingBulkOperations()
        {
            if (pendingRemovalKeys.Count == 0 && pendingSetOperationsDictionary.Count == 0)
                return;

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    DateTime startTime = DateTime.UtcNow;

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (SQLiteTransaction tran = connection.BeginTransaction())
                        {

                            #region Remove Operations

                            foreach (string key in pendingRemovalKeys)
                            {
                                using (var command = new SQLiteCommand("delete from KeyValue where Key = @key", connection))
                                {
                                    command.Parameters.Add("@key", DbType.String, key.Length).Value = key;

                                    command.ExecuteNonQuery();
                                }
                            }

                            #endregion

                            #region Set operations

                            foreach (var key in pendingSetOperationsDictionary.Keys)
                            {
                                var data = pendingSetOperationsDictionary[key];

                                bool isExists = false;

                                using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                                {


                                    command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                    int count = Convert.ToInt32(command.ExecuteScalar());

                                    if (count > 0)
                                        isExists = true;
                                }

                                if (isExists == true)
                                {
                                    using (var command = new SQLiteCommand("update KeyValue set DATA=@data where Key = @key", connection))
                                    {
                                        command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                        if (data != null)
                                            command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                        else
                                            command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                        command.ExecuteNonQuery();
                                    }
                                }
                                else
                                {
                                    using (var command = new SQLiteCommand("INSERT INTO KeyValue (KEY,DATA) VALUES (@key,@data)", connection))
                                    {
                                        command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                                        if (data != null)
                                            command.Parameters.Add("@data", DbType.Binary, data.Length).Value = data;
                                        else
                                            command.Parameters.Add("@data", DbType.Binary, 0).Value = null;

                                        command.ExecuteNonQuery();
                                    }
                                }
                            }

                            #endregion

                            tran.Commit();

                            File.Delete(Path.Combine(folderPath, "Removables.db"));
                            File.Delete(Path.Combine(folderPath, "Settables.db"));

                            AddToFrequentKeys(pendingSetOperationsDictionary.Keys.ToList());

                            pendingRemovalKeys.Clear();
                            pendingSetOperationsDictionary.Clear();
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();

                        DateTime endTime = DateTime.UtcNow;

                        TempLogger.LogCustom("sqlitePerformance.txt", "Bulk Operation Time taken..." + (endTime - startTime).TotalMilliseconds.ToString() + " Milliseconds.");
                    }
                }
            }
        }

        private void AddToFrequentKeys(List<string> keys)
        {
            if (keys != null)
            {
                foreach (string key in keys)
                {
                    AddToFrequentKeys(key);
                }
            }
        }

        private void AddToFrequentKeys(string key)
        {
            if (!FrequentKeys.ContainsKey(key))
                FrequentKeys.Add(key, true);

        }

        private void RemoveFromFrequentKeys(string key)
        {
            if (FrequentKeys.ContainsKey(key))
            {
                FrequentKeys.Remove(key);
            }
        }

        public byte[] Get(string key)
        {
            key = key.Trim();

            lock (collectionLock)
            {
                if (pendingRemovalKeys.Contains(key))
                    return null;
                else
                    if (pendingSetOperationsDictionary.Keys.Contains(key))
                    {
                        AddToFrequentKeys(key);
                        return pendingSetOperationsDictionary[key];
                    }
            }

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {

                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        using (var command = new SQLiteCommand("SELECT DATA FROM KeyValue WHERE KEY=@key", connection))
                        {
                            DateTime startTime = DateTime.UtcNow;
                            connection.Open();
                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            byte[] buffer = null;
                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    buffer = GetBytes(reader);
                                }
                            }
                            AddToFrequentKeys(key);

                            DateTime endTime = DateTime.UtcNow;

                            TempLogger.LogCustom("sqlitePerformance.txt", "Get Operation Time taken..." + (endTime - startTime).TotalMilliseconds.ToString() + " Milliseconds.");


                            return buffer;
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }


                }
            }
        }

        public List<byte[]> GetAll()
        {
            List<byte[]> dataInList = new List<byte[]>();

            lock (collectionLock)
            {
                dataInList.AddRange(pendingSetOperationsDictionary.Values.ToList());
            }

            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);
                    try
                    {
                        using (var command = new SQLiteCommand("SELECT DATA FROM KeyValue", connection))
                        {
                            connection.Open();
                            byte[] buffer = null;
                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    buffer = null;
                                    buffer = GetBytes(reader);

                                    if (buffer != null)
                                        dataInList.Add(buffer);
                                }
                            }

                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return dataInList;

        }

        public bool IsExists(string key)
        {
            lock (collectionLock)
            {
                if (pendingRemovalKeys.Contains(key))
                    return false;

                if (frequentKeys.ContainsKey(key))
                {
                    return true;
                }

                if (pendingSetOperationsDictionary.Keys.Contains(key))
                {
                    AddToFrequentKeys(key);

                    return true;
                }
            }

            bool isExists = false;
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);
                    try
                    {
                        using (var command = new SQLiteCommand("select count(rowid)  from KeyValue where Key = @key", connection))
                        {
                            connection.Open();

                            command.Parameters.Add("@key", DbType.String, key.Length).Value = key;
                            int count = Convert.ToInt32(command.ExecuteScalar());

                            if (count > 0)
                                isExists = true;

                            AddToFrequentKeys(key);

                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return isExists;

        }

        public int GetCount()
        {
            using (FolderSyncLocker folderLocker = new FolderSyncLocker(folderPath))
            {
                lock (syncObject)
                {
                    var connection = new SQLiteConnection(connectionString);

                    try
                    {
                        connection.Open();

                        using (var command = new SQLiteCommand("select count(rowid)  from KeyValue", connection))
                        {

                            int count = Convert.ToInt32(command.ExecuteScalar());

                            return count + pendingSetOperationsDictionary.Count;
                        }
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public bool AreExists(string[] keys)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Helper Methods

        static byte[] GetBytes(SQLiteDataReader reader)
        {
            const int CHUNK_SIZE = 2 * 1024;
            byte[] buffer = new byte[CHUNK_SIZE];
            long bytesRead;
            long fieldOffset = 0;
            using (MemoryStream stream = new MemoryStream())
            {
                while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
                {
                    stream.Write(buffer, 0, (int)bytesRead);
                    fieldOffset += bytesRead;
                }
                return stream.ToArray();
            }
        }

        #endregion

    }
}